<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}
require_once JPATH_BASE.'/includes/framework.php';
// Mark afterLoad in the profiler.
// Instantiate the application.
$app = JFactory::getApplication('site');
// Initialise the application.
$app->initialise();
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("Wmc")
                             ->setLastModifiedBy("Wmc")
                             ->setTitle("Redemption Report New")
                             ->setSubject("Redemption Report New")
                             ->setDescription("Redemption Report New")
                             ->setKeywords("Redemption Report")
                             ->setCategory("Redemption Report");
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('B1:G1');
$activeSheet->setCellValue('B1', 'Accounting Redemption Summary Report');
$activeSheet->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('B1')->getFont()->setSize(24);
$activeSheet->getStyle('B1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 7;
$activeSheet->setCellValue('A'.$index, 'No');
$activeSheet->setCellValue('B'.$index, 'Outlet');
$activeSheet->setCellValue('C'.$index, 'Item');
$activeSheet->setCellValue('D'.$index, 'Quantity');
$activeSheet->setCellValue('E'.$index, 'Charge Cost');


$cols = array('A', 'B', 'C', 'D', 'E',);

foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, 7, 10);
    }
	if($item == 'B')
    {
        setBackgroundColor($activeSheet, $item, 7, 40);
    }
	if($item == 'C')
    {
        setBackgroundColor($activeSheet, $item, 7, 40);
    }
    else if($item == 'D')
    {
        setBackgroundColor($activeSheet, $item, 7, 10);
    }
    else if($item == 'E')
    {
        setBackgroundColor($activeSheet, $item, 7, 20);   
    }
}

// End init excel

/******** Query Data ********/  
$db = JFactory::getDbo();
$conditions = array();
$conditions['dateFrom'] = strval($_GET['df']);
$conditions['dateTo'] = strval($_GET['dt']);

$conditions['filter_field'] = strval($_GET['ff']);
$conditions['search_value'] = strval($_GET['sv']);
$conditions['filter'] = strval($_GET['f']);

$conditions['status'] = strval($_GET['s']);
$conditions['products'] = strval($_GET['p']);
$conditions['outlet'] = strval($_GET['o']);
$data = reportProvider::redemptionreportnewsummary($conditions, $db, intval($_GET['l']));

//echo '<pre>';print_r($data);
$arr = array();
     if(count($data['items'])){
        foreach($data['items'] as $item){
            $outlet_id    = $item->outletName;
            $product_id   = $item->product_id;
            $quantity     = $item->quantity;
            $charge_cost  = $item->charge_cost;
            $product_name = $item->productName;

            if(!isset($arr[$outlet_id])) $arr[$outlet_id] = array();                    
            if(!isset($arr[$outlet_id][$product_id])){
                $arr[$outlet_id][$product_id] = array(
                    "productName" => $product_name,
                    "quantity"    => $quantity,
                    "charge_cost" => $charge_cost
                );
            }else{
                $arr[$outlet_id][$product_id]['quantity']    += $quantity;
                $arr[$outlet_id][$product_id]['charge_cost'] += $charge_cost;
            }
        }
    }	
//echo "<hr />";echo '<pre>';print_r($arr); die;
if(!empty($data))
{
    $activeSheet->setCellValue('B3', 'From: ');
	$activeSheet->setCellValue('B4', 'To: ');
	$activeSheet->setCellValue('C3',$conditions['dateFrom']);
	$activeSheet->setCellValue('C4', $conditions['dateTo']);
	$activeSheet->setCellValue('F3', 'Status');	
    $activeSheet->setCellValue('G3', 'Redeemed');
    /*********** BIND TO EXCEL ***********/
    $index = 8;
    $data = $data['items'];
    $size = count($arr);
    $approve = array(
        '0' => 'Waiting',
        '1' => 'Approve',
        '2' => 'UnApprove'
    );
    $count=0;
    foreach($arr as $k => $v)
    {
        
		$activeSheet->setCellValue('A'.$index, $count); 
        $activeSheet->getStyle('A'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $activeSheet->setCellValue('B'.$index, $k);    
        $activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$size=count($v);
        $j=0;
		foreach($v as $key => $value){
			$activeSheet->setCellValue('C'.($index+$j), $value['productName']);                           
			$activeSheet->setCellValue('D'.($index+$j), $value['quantity']);                
			$activeSheet->getStyle('D'.($index+$j))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			
			$activeSheet->setCellValue('E'.($index+$j),$value['charge_cost']);
			$activeSheet->getStyle('E'.($index+$j))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$j++;
		}
		
		$index+=count($v);
		$count++;
    }
	
    unset($data);
}

$fileName = 'redemption_report_new_summary_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          


function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
